﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections;
using System.Data.SqlClient;
using System.Configuration;
using Trying1;
using System.Data;
using System.Data.SqlTypes;

namespace Trying1.Classes
{
    class GuestBookingDBManager
    {
        
        public static ArrayList GetGuestBylastName(string lastName)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select g.guestNo,g.firstName,g.lastName,b.checkInDate,b.checkOutDate,b.addReq,g.roomNum,b.roomRates from Guest g,Booking b where lastName=@lastName and g.guestNo=b.guestNo;";
                comm.Parameters.AddWithValue("@lastName", lastName);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    GuestBooking m = new GuestBooking();
                    m.guestNo = (int)dr["guestNo"];
                    m.firstName = (string)dr["firstName"];
                    m.lastName = (string)dr["lastName"];
                    m.roomNum = (int)dr["roomNum"];
                    m.checkInDate = (DateTime)dr["checkInDate"];
                    m.checkOutDate = (DateTime)dr["checkOutDate"];
                    m.addReq = (string)dr["addReq"];
                    m.roomRates = (decimal)dr["roomRates"];
                    result.Add(m);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }
        public static GuestBooking GetGuestBylastnameguestno(int guestNo, string lastName)
        {
            GuestBooking m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select g.guestNo,g.firstName,g.lastName,b.checkInDate,b.checkOutDate,b.addReq,g.roomNum,b.roomRates from Guest g,Booking b where lastName=@lastName and g.guestNo=@guestNo and g.guestNo=b.guestNo;";
                comm.Parameters.AddWithValue("@guestNo", guestNo);
                comm.Parameters.AddWithValue("@lastName", lastName);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new GuestBooking();
                    m.guestNo = (int)dr["guestNo"];
                    m.firstName = (string)dr["firstName"];
                    m.lastName = (string)dr["lastName"];
                    m.roomNum = (int)dr["roomNum"];
                    m.checkInDate = (DateTime)dr["checkInDate"];
                    m.checkOutDate = (DateTime)dr["checkOutDate"];
                    m.addReq = (string)dr["addReq"];
                    m.roomRates = (decimal)dr["roomRates"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }
        public static GuestBooking GetCheckInByAll(int guestNo, string lastName, DateTime checkInDate, DateTime checkOutDate)
        {
            GuestBooking m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select g.guestNo,g.firstName,g.lastName,b.checkInDate,b.checkOutDate,b.addReq,g.roomNum,b.roomRates from Guest g,Booking b where guestNo=@guestNo and lastName=@lastName and checkInDate=@checkInDate and checkOutDate=@checkOutDate and g.guestNo=b.guestNo;";

                comm.Parameters.AddWithValue("@guestNo", guestNo);
                comm.Parameters.AddWithValue("@lastName", lastName);
                comm.Parameters.AddWithValue("@checkInDate", checkInDate);
                comm.Parameters.AddWithValue("@checkOutDate", checkOutDate);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new GuestBooking();
                    m.guestNo = (int)dr["guestNo"];
                    m.firstName = (string)dr["firstName"];
                    m.lastName = (string)dr["lastName"];
                    m.roomNum = (int)dr["roomNum"];
                    m.checkInDate = (DateTime)dr["checkInDate"];
                    m.checkOutDate = (DateTime)dr["checkOutDate"];
                    m.addReq = (string)dr["addReq"];
                    m.roomRates = (decimal)dr["roomRates"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }
        public static ArrayList GetCheckInlastNameCIDate(string lastName, DateTime checkInDate)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select g.guestNo,g.firstName,g.lastName,b.checkInDate,b.checkOutDate,b.addReq,g.roomNum,b.roomRates from Guest g,Booking b where lastName=@lastName and checkInDate=@checkInDate and g.guestNo=b.guestNo;";
                comm.Parameters.AddWithValue("@lastName", lastName);
                comm.Parameters.AddWithValue("@checkInDate", checkInDate);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    GuestBooking m = new GuestBooking();
                    m.guestNo = (int)dr["guestNo"];
                    m.firstName = (string)dr["firstName"];
                    m.lastName = (string)dr["lastName"];
                    m.roomNum = (int)dr["roomNum"];
                    m.checkInDate = (DateTime)dr["checkInDate"];
                    m.checkOutDate = (DateTime)dr["checkOutDate"];
                    m.addReq = (string)dr["addReq"];
                    m.roomRates = (decimal)dr["roomRates"];

                    result.Add(m);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static GuestBooking GetCheckInByguestNo(int guestNo)
        {
            GuestBooking m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select g.guestNo,g.firstName,g.lastName,b.checkInDate,b.checkOutDate,b.addReq,g.roomNum,b.roomRates from Guest g,Booking b where g.guestNo=@guestNo and g.guestNo=b.guestNo;";

                comm.Parameters.AddWithValue("@guestNo", guestNo);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new GuestBooking();
                    m.guestNo = (int)dr["guestNo"];
                    m.firstName = (string)dr["firstName"];
                    m.lastName = (string)dr["lastName"];
                    m.roomNum = (int)dr["roomNum"];
                    m.checkInDate = (DateTime)dr["checkInDate"];
                    m.checkOutDate = (DateTime)dr["checkOutDate"];
                    m.addReq = (string)dr["addReq"];
                    m.roomRates = (decimal)dr["roomRates"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }
        public static ArrayList GetCheckInBylastnamecico(string lastName, DateTime ccid, DateTime ccod)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select g.guestNo,g.firstName,g.lastName,b.checkInDate,b.checkOutDate,b.addReq,g.roomNum,b.roomRates from Guest g,Booking b where lastName=@lastName and b.checkInDate=@checkInDate and b.checkOutDate=@checkOutDate and g.guestNo=b.guestNo;";
                comm.Parameters.AddWithValue("@lastName", lastName);
                comm.Parameters.AddWithValue("@checkInDate", ccid);
                comm.Parameters.AddWithValue("@checkOutDate", ccod);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    GuestBooking m = new GuestBooking();
                    m.guestNo = (int)dr["guestNo"];
                    m.firstName = (string)dr["firstName"];
                    m.lastName = (string)dr["lastName"];
                    m.roomNum = (int)dr["roomNum"];
                    m.checkInDate = (DateTime)dr["checkInDate"];
                    m.checkOutDate = (DateTime)dr["checkOutDate"];
                    m.addReq = (string)dr["addReq"];
                    m.roomRates = (decimal)dr["roomRates"];
                    result.Add(m);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }
    }
}
